﻿using Dapper;
using SIG.DAL.Dapper.Model;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIG.DAL.Dapper.Repositories
{
    public class PostRepository
    {
        private DbConnection _connection;

        public Post GetById(int id)
        {
            Post result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<Post>(id);
            }
            return result;
        }
        public int? Create(Post model)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Insert(model);
            }
            return result;
        }

        public int Update(Post model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Update(model);
            }
            return result;
        }
        public int Delete(Post model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<Post>(id);
            }
            return result;
        }

        public IEnumerable<Post> GetActiveItems()
        {
            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Post>(new { Active = true });
            }
            return result;
        }

        public Post GetPrev(int id)
        {
            Post result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Post>( "where Active = 1 and Id < @Id", new { Id = id })
                    .OrderByDescending(m=>m.Id).FirstOrDefault();
            }
            return result;
        }
        public Post GetNext(int id)
        {
            Post result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<Post>("where Active = 1 and Id > @Id", new { Id = id })
                    .OrderByDescending(m => m.Id).FirstOrDefault();
            }
            return result;
        }
        public IEnumerable<Post> GetListPagedAcitveItems(int pageIndex, int pageSize, int categoryId)
        {
            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetListPaged<Post>(pageIndex, pageSize, "where Active=1 and CategoryId = @CategoryId", "CreatedDate desc", new { CategoryId = categoryId });
            }
            return result;
        }


        public IEnumerable<Post> GetListPagedAcitveItems(int pageIndex, int pageSize, int parentId, string keyword, out int totalCount)
        {

            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                var sql = "where Active=1 ";

                if (!string.IsNullOrEmpty(keyword))
                {
                    sql = sql + " and Title like @Keyword AND Body like @Keyword";
                }
                if (parentId > 0)
                {
                    sql = sql + " and CategoryId = @CategoryId ";
                }

                totalCount = _connection.RecordCount<Post>(sql,
                    new { CategoryId = parentId, Keyword = likeKeyword });
                result = _connection.GetListPaged<Post>(pageIndex, pageSize,
                    sql, "CreatedDate desc",
                    new { CategoryId = parentId, Keyword = likeKeyword });
            }
            return result;
        }

        public IEnumerable<Post> GetListPagedRecommendItems(int pageIndex, int pageSize, int parentId, string keyword, out int totalCount)
        {

            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                var sql = "where Active=1 AND Recommend =1 ";

                if (!string.IsNullOrEmpty(keyword))
                {
                    sql = sql + " and Title like @Keyword AND Body like @Keyword";
                }
                if (parentId > 0)
                {
                    sql = sql + " and CategoryId = @CategoryId ";
                }

                totalCount = _connection.RecordCount<Post>(sql,
                    new { CategoryId = parentId, Keyword = likeKeyword });
                result = _connection.GetListPaged<Post>(pageIndex, pageSize,
                    sql, "CreatedDate desc",
                    new { CategoryId = parentId, Keyword = likeKeyword });
            }
            return result;
        }

        public IEnumerable<Post> GetListPagedRecommendPosts(int pageIndex, int pageSize)
        {            
            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
               
                result = _connection.GetListPaged<Post>(pageIndex, pageSize, "where Active=1 and Recommend = 1", "CreatedDate desc");
            }
            return result;
        }
     
        public IEnumerable<Post> GetListPagedRecommendPosts(int pageIndex, int pageSize, out int totalCount)
        {          

            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                totalCount = _connection.RecordCount<Post>("where Active = 1 and Recommend = 1");
                result = _connection.GetListPaged<Post>(pageIndex, pageSize, "where Active=1 and Recommend = 1", "CreatedDate desc");
            }
            return result;
        }
        /// <summary>
        /// 获取所有文章分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="parentId"></param>
        /// <param name="keyword"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public IEnumerable<Post> GetListPagedItems(int pageIndex, int pageSize, int parentId,string keyword, out int totalCount)
        {
            if (parentId == 0)
            {
                int count;
                var list = GetListPagedItems(pageIndex, pageSize, keyword, out count);
                totalCount = count;
                return list;
            }
               
            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                totalCount = _connection.RecordCount<Post>("where CategoryId = @CategoryId and (Title like @Title or Body like @Body)",
                    new { CategoryId= parentId , Title = likeKeyword ,Body = likeKeyword });
                result = _connection.GetListPaged<Post>(pageIndex, pageSize, 
                    "where CategoryId = @CategoryId and (Title like @Title or Body like @Body)", "CreatedDate desc",
                    new { CategoryId = parentId, Title = likeKeyword, Body = likeKeyword });
            }
            return result;
        }
        public IEnumerable<Post> GetListPagedItems(int pageIndex, int pageSize,  string keyword, out int totalCount)
        {
            IEnumerable<Post> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                totalCount = _connection.RecordCount<Post>("where Title like @Title or Body like @Body",
                    new { Title = likeKeyword, Body = likeKeyword });
                result = _connection.GetListPaged<Post>(pageIndex, pageSize,
                    "where Title like @Title or Body like @Body", "CreatedDate desc",
                    new { Title = likeKeyword, Body = likeKeyword });
            }
            return result;
        }


        public int GetCountByParentId(int parentId)
        {

            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.RecordCount<Post>("where CategoryId = @CategoryId",new { CategoryId=parentId});              
            }
            return result;
        }
    }

}
